package ig.metrica.ejb.conflujos.dao;


import ig.da.abs.AbstractBasicDao;
import java.sql.SQLException;
import java.lang.Exception;
import ig.metrica.ejb.conflujos.ConfiguracionflujosformuladosDTO;
import ig.metrica.ejb.conflujos.ConfiguracionflujosformuladosPK;

/**
 * GenJ 2011
 * @author usuario
 *
 * Is an Class for table DB for DAO
 */
public class ConfiguracionflujosformuladosDAO extends AbstractBasicDao{

	// -----------------------------------------------------------------
	// Constants
	// -----------------------------------------------------------------

	// -----------------------------------------------------------------
	// Attributes
	// -----------------------------------------------------------------
    private java.sql.Connection connection = null;

	// -----------------------------------------------------------------
	// Constructors
	// -----------------------------------------------------------------
    public ConfiguracionflujosformuladosDAO ()  {

    }

	// -----------------------------------------------------------------
	// All Methods
	// -----------------------------------------------------------------

                                                                                

    /**
     * Method for create an record of table of table
     * @return : the pk of table
     * @param dto
     * @throws SQLException
     * @throws Exception
     * @throws Throwable
     */
    public  ig.metrica.ejb.conflujos.ConfiguracionflujosformuladosPK  create(ConfiguracionflujosformuladosDTO dto) throws SQLException, Exception, Throwable {
        java.sql.PreparedStatement prep = null;
        connection = open();
        java.sql.Statement stat = null;
        java.sql.ResultSet res = null;

        try{
            String QUERY = "INSERT INTO CONFIGURACIONFLUJOSFORMULADOS ( ID, IDMODELO, PRODUCTO, FORMULA_CAPITAL, FORMULA_INTERES_FIJO, FORMULA_INTERES_VARIABLE, FORMULA_FECHA, ACTIVO ) VALUES ( ?,  ?,  ?,  ?,  ?,  ?,  ?,  ? )";

            prep = connection.prepareStatement(QUERY);
            prep.setInt (1,dto.getId());
            prep.setInt (2,dto.getIdmodelo());
            prep.setString (3,dto.getProducto());
            prep.setString (4,dto.getFormula_capital());
            prep.setString (5,dto.getFormula_interes_fijo());
            prep.setString (6,dto.getFormula_interes_variable());
            prep.setString (7,dto.getFormula_fecha());
            prep.setInt (8,dto.getActivo());
            prep.executeUpdate();
        } catch (SQLException ex) {
            throw ex;
        } catch (Exception ex) {
            throw ex;
        } finally {
            close(prep, connection, stat, res);
        }
        return dto.getPk();
    }


    /**
     * Update by Pk
     * @param dto
     * @return teh number record updates
     * @throws SQLException
     * @throws Exception
     * @throws Throwable
     */
    public int update (ConfiguracionflujosformuladosDTO dto) throws SQLException, Exception, Throwable {
        java.sql.PreparedStatement prep = null;
        connection = open();
        try{
            String QUERY = "UPDATE CONFIGURACIONFLUJOSFORMULADOS SET FORMULA_CAPITAL = ?, FORMULA_INTERES_FIJO = ?, FORMULA_INTERES_VARIABLE = ?, FORMULA_FECHA = ?, ACTIVO = ?, , WHERE ID = ? AND IDMODELO = ? AND PRODUCTO = ?";
            prep = connection.prepareStatement(QUERY);
            prep.setString (1,dto.getFormula_capital());
            prep.setString (2,dto.getFormula_interes_fijo());
            prep.setString (3,dto.getFormula_interes_variable());
            prep.setString (4,dto.getFormula_fecha());
            prep.setInt (5,dto.getActivo());

            prep.setInt (6,dto.getId());
            prep.setInt (7,dto.getIdmodelo());
            prep.setString (8,dto.getProducto());
            return prep.executeUpdate();
        } catch (SQLException ex) {
            throw ex;
        } catch (Exception ex) {
            throw ex;
        } finally {
            close(prep,connection);
        }
    }

    /**
     * Delete by Pk
     * @param pk
     * @return the number record delete
     * @throws SQLException
     * @throws Exception
     * @throws Throwable
     */
    public int delete (ConfiguracionflujosformuladosPK pk ) throws SQLException, Exception, Throwable {
        java.sql.PreparedStatement prep = null;
        connection = open();

        try{
            String QUERY = "DELETE FROM CONFIGURACIONFLUJOSFORMULADOS WHERE ID = ? AND IDMODELO = ? AND PRODUCTO = ?";
            prep = connection.prepareStatement( QUERY );
            prep.setInt (1,pk .getId());
            prep.setInt (2,pk .getIdmodelo());
            prep.setString (3,pk .getProducto());
            
            return prep.executeUpdate();
        } catch (SQLException ex) {
            throw ex;
        } catch (Exception ex) {
            throw ex;
        } finally {
            close(prep,connection);
        }
    }

    /**
     * Find by Pk
     * @param pk
     * @return DTO
     * @throws SQLException
     * @throws Exception
     * @throws Throwable
     */
    public ConfiguracionflujosformuladosDTO findByPk (ConfiguracionflujosformuladosPK pk ) throws SQLException, Exception, Throwable {
        java.sql.PreparedStatement prep = null;
        java.sql.ResultSet res = null;
        connection = open();
        ConfiguracionflujosformuladosDTO configuracionflujosformulados = null;
        try{
            String QUERY = "SELECT ID, IDMODELO, PRODUCTO, FORMULA_CAPITAL, FORMULA_INTERES_FIJO, FORMULA_INTERES_VARIABLE, FORMULA_FECHA, ACTIVO FROM CONFIGURACIONFLUJOSFORMULADOS WHERE ID = ? AND IDMODELO = ? AND PRODUCTO = ?";
            prep = connection.prepareStatement( QUERY );
            prep.setInt (1,pk .getId());
            prep.setInt (2,pk .getIdmodelo());
            prep.setString (3,pk .getProducto());
            res = prep.executeQuery();
            if (res.next()) {
                configuracionflujosformulados = new ConfiguracionflujosformuladosDTO(  res.getInt ( 1 ), res.getInt ( 2 ), res.getString ( 3 ), res.getString ( 4 ), res.getString ( 5 ), res.getString ( 6 ), res.getString ( 7 ), res.getInt ( 8 ));
            }
        } catch (SQLException ex) {
            throw ex;
        } catch (Exception ex) {
            throw ex;
        } finally {
            close(prep,res,connection);
        }
        return configuracionflujosformulados;
    }




    public java.util.List< ig.metrica.ejb.conflujos.ConfiguracionflujosformuladosPK > createAll(java.util.List<ConfiguracionflujosformuladosDTO> dtos) throws SQLException, Exception, Throwable {
        java.sql.PreparedStatement prep = null;
        connection = open();
        java.sql.Statement stat = null;
        java.sql.ResultSet res = null;
        java.util.List< ig.metrica.ejb.conflujos.ConfiguracionflujosformuladosPK > list=new java.util.ArrayList< ig.metrica.ejb.conflujos.ConfiguracionflujosformuladosPK >();
        try{
            String QUERY = "INSERT INTO CONFIGURACIONFLUJOSFORMULADOS ( ID, IDMODELO, PRODUCTO, FORMULA_CAPITAL, FORMULA_INTERES_FIJO, FORMULA_INTERES_VARIABLE, FORMULA_FECHA, ACTIVO ) VALUES ( ?,  ?,  ?,  ?,  ?,  ?,  ?,  ? )";
            for(ConfiguracionflujosformuladosDTO dto : dtos ){

                prep = connection.prepareStatement(QUERY);
                prep.setInt (1,dto.getId());
                prep.setInt (2,dto.getIdmodelo());
                prep.setString (3,dto.getProducto());
                prep.setString (4,dto.getFormula_capital());
                prep.setString (5,dto.getFormula_interes_fijo());
                prep.setString (6,dto.getFormula_interes_variable());
                prep.setString (7,dto.getFormula_fecha());
                prep.setInt (8,dto.getActivo());
                prep.executeUpdate();
                list.add(dto.getPk());
            }
        } catch (SQLException ex) {
            throw ex;
        } catch (Exception ex) {
            throw ex;
        } finally {
            close(prep, connection, stat, res);
        }
        return list;
    }


    /**
     * The List By id
     * @param id
     * @return list of ConfiguracionflujosformuladosDTO
     */
    public java.util.List<ConfiguracionflujosformuladosDTO> findById (Integer  id) throws SQLException, Exception, Throwable {

        java.sql.PreparedStatement prep = null;
        java.sql.ResultSet res = null;
        connection = open();
        java.util.List<ConfiguracionflujosformuladosDTO> list=new java.util.ArrayList<ConfiguracionflujosformuladosDTO>();

        try{
            String QUERY = "SELECT ID, IDMODELO, PRODUCTO, FORMULA_CAPITAL, FORMULA_INTERES_FIJO, FORMULA_INTERES_VARIABLE, FORMULA_FECHA, ACTIVO FROM CONFIGURACIONFLUJOSFORMULADOS WHERE ID = ?";
            prep = connection.prepareStatement( QUERY );
            prep.setInt (1,id);
            res = prep.executeQuery();
            while (res.next()) {
                list.add(new ConfiguracionflujosformuladosDTO(  (Integer)res.getObject ( 1 ), (Integer)res.getObject ( 2 ), (String)res.getObject ( 3 ), (String)res.getObject ( 4 ), (String)res.getObject ( 5 ), (String)res.getObject ( 6 ), (String)res.getObject ( 7 ), (Integer)res.getObject ( 8 )));
            }
            return list;
        } catch (SQLException ex) {
            throw ex;
        } catch (Exception ex) {
            throw ex;
        } finally {
            close(prep,connection);
        }
    }

    /**
     * The List By idmodelo
     * @param idmodelo
     * @return list of ConfiguracionflujosformuladosDTO
     */
    public java.util.List<ConfiguracionflujosformuladosDTO> findByIdmodelo (Integer  idmodelo) throws SQLException, Exception, Throwable {

        java.sql.PreparedStatement prep = null;
        java.sql.ResultSet res = null;
        connection = open();
        java.util.List<ConfiguracionflujosformuladosDTO> list=new java.util.ArrayList<ConfiguracionflujosformuladosDTO>();

        try{
            String QUERY = "SELECT ID, IDMODELO, PRODUCTO, FORMULA_CAPITAL, FORMULA_INTERES_FIJO, FORMULA_INTERES_VARIABLE, FORMULA_FECHA, ACTIVO FROM CONFIGURACIONFLUJOSFORMULADOS WHERE IDMODELO = ?";
            prep = connection.prepareStatement( QUERY );
            prep.setInt (1,idmodelo);
            res = prep.executeQuery();
            while (res.next()) {
                list.add(new ConfiguracionflujosformuladosDTO(  (Integer)res.getObject ( 1 ), (Integer)res.getObject ( 2 ), (String)res.getObject ( 3 ), (String)res.getObject ( 4 ), (String)res.getObject ( 5 ), (String)res.getObject ( 6 ), (String)res.getObject ( 7 ), (Integer)res.getObject ( 8 )));
            }
            return list;
        } catch (SQLException ex) {
            throw ex;
        } catch (Exception ex) {
            throw ex;
        } finally {
            close(prep,connection);
        }
    }

    /**
     * The List By producto
     * @param producto
     * @return list of ConfiguracionflujosformuladosDTO
     */
    public java.util.List<ConfiguracionflujosformuladosDTO> findByProducto (String  producto) throws SQLException, Exception, Throwable {

        java.sql.PreparedStatement prep = null;
        java.sql.ResultSet res = null;
        connection = open();
        java.util.List<ConfiguracionflujosformuladosDTO> list=new java.util.ArrayList<ConfiguracionflujosformuladosDTO>();

        try{
            String QUERY = "SELECT ID, IDMODELO, PRODUCTO, FORMULA_CAPITAL, FORMULA_INTERES_FIJO, FORMULA_INTERES_VARIABLE, FORMULA_FECHA, ACTIVO FROM CONFIGURACIONFLUJOSFORMULADOS WHERE PRODUCTO = ?";
            prep = connection.prepareStatement( QUERY );
            prep.setString (1,producto);
            res = prep.executeQuery();
            while (res.next()) {
                list.add(new ConfiguracionflujosformuladosDTO(  (Integer)res.getObject ( 1 ), (Integer)res.getObject ( 2 ), (String)res.getObject ( 3 ), (String)res.getObject ( 4 ), (String)res.getObject ( 5 ), (String)res.getObject ( 6 ), (String)res.getObject ( 7 ), (Integer)res.getObject ( 8 )));
            }
            return list;
        } catch (SQLException ex) {
            throw ex;
        } catch (Exception ex) {
            throw ex;
        } finally {
            close(prep,connection);
        }
    }

    /**
     * The List By formula_capital
     * @param formula_capital
     * @return list of ConfiguracionflujosformuladosDTO
     */
    public java.util.List<ConfiguracionflujosformuladosDTO> findByFormula_capital (String  formula_capital) throws SQLException, Exception, Throwable {

        java.sql.PreparedStatement prep = null;
        java.sql.ResultSet res = null;
        connection = open();
        java.util.List<ConfiguracionflujosformuladosDTO> list=new java.util.ArrayList<ConfiguracionflujosformuladosDTO>();

        try{
            String QUERY = "SELECT ID, IDMODELO, PRODUCTO, FORMULA_CAPITAL, FORMULA_INTERES_FIJO, FORMULA_INTERES_VARIABLE, FORMULA_FECHA, ACTIVO FROM CONFIGURACIONFLUJOSFORMULADOS WHERE FORMULA_CAPITAL = ?";
            prep = connection.prepareStatement( QUERY );
            prep.setString (1,formula_capital);
            res = prep.executeQuery();
            while (res.next()) {
                list.add(new ConfiguracionflujosformuladosDTO(  (Integer)res.getObject ( 1 ), (Integer)res.getObject ( 2 ), (String)res.getObject ( 3 ), (String)res.getObject ( 4 ), (String)res.getObject ( 5 ), (String)res.getObject ( 6 ), (String)res.getObject ( 7 ), (Integer)res.getObject ( 8 )));
            }
            return list;
        } catch (SQLException ex) {
            throw ex;
        } catch (Exception ex) {
            throw ex;
        } finally {
            close(prep,connection);
        }
    }

    /**
     * The List By formula_interes_fijo
     * @param formula_interes_fijo
     * @return list of ConfiguracionflujosformuladosDTO
     */
    public java.util.List<ConfiguracionflujosformuladosDTO> findByFormula_interes_fijo (String  formula_interes_fijo) throws SQLException, Exception, Throwable {

        java.sql.PreparedStatement prep = null;
        java.sql.ResultSet res = null;
        connection = open();
        java.util.List<ConfiguracionflujosformuladosDTO> list=new java.util.ArrayList<ConfiguracionflujosformuladosDTO>();

        try{
            String QUERY = "SELECT ID, IDMODELO, PRODUCTO, FORMULA_CAPITAL, FORMULA_INTERES_FIJO, FORMULA_INTERES_VARIABLE, FORMULA_FECHA, ACTIVO FROM CONFIGURACIONFLUJOSFORMULADOS WHERE FORMULA_INTERES_FIJO = ?";
            prep = connection.prepareStatement( QUERY );
            prep.setString (1,formula_interes_fijo);
            res = prep.executeQuery();
            while (res.next()) {
                list.add(new ConfiguracionflujosformuladosDTO(  (Integer)res.getObject ( 1 ), (Integer)res.getObject ( 2 ), (String)res.getObject ( 3 ), (String)res.getObject ( 4 ), (String)res.getObject ( 5 ), (String)res.getObject ( 6 ), (String)res.getObject ( 7 ), (Integer)res.getObject ( 8 )));
            }
            return list;
        } catch (SQLException ex) {
            throw ex;
        } catch (Exception ex) {
            throw ex;
        } finally {
            close(prep,connection);
        }
    }

    /**
     * The List By formula_interes_variable
     * @param formula_interes_variable
     * @return list of ConfiguracionflujosformuladosDTO
     */
    public java.util.List<ConfiguracionflujosformuladosDTO> findByFormula_interes_variable (String  formula_interes_variable) throws SQLException, Exception, Throwable {

        java.sql.PreparedStatement prep = null;
        java.sql.ResultSet res = null;
        connection = open();
        java.util.List<ConfiguracionflujosformuladosDTO> list=new java.util.ArrayList<ConfiguracionflujosformuladosDTO>();

        try{
            String QUERY = "SELECT ID, IDMODELO, PRODUCTO, FORMULA_CAPITAL, FORMULA_INTERES_FIJO, FORMULA_INTERES_VARIABLE, FORMULA_FECHA, ACTIVO FROM CONFIGURACIONFLUJOSFORMULADOS WHERE FORMULA_INTERES_VARIABLE = ?";
            prep = connection.prepareStatement( QUERY );
            prep.setString (1,formula_interes_variable);
            res = prep.executeQuery();
            while (res.next()) {
                list.add(new ConfiguracionflujosformuladosDTO(  (Integer)res.getObject ( 1 ), (Integer)res.getObject ( 2 ), (String)res.getObject ( 3 ), (String)res.getObject ( 4 ), (String)res.getObject ( 5 ), (String)res.getObject ( 6 ), (String)res.getObject ( 7 ), (Integer)res.getObject ( 8 )));
            }
            return list;
        } catch (SQLException ex) {
            throw ex;
        } catch (Exception ex) {
            throw ex;
        } finally {
            close(prep,connection);
        }
    }

    /**
     * The List By formula_fecha
     * @param formula_fecha
     * @return list of ConfiguracionflujosformuladosDTO
     */
    public java.util.List<ConfiguracionflujosformuladosDTO> findByFormula_fecha (String  formula_fecha) throws SQLException, Exception, Throwable {

        java.sql.PreparedStatement prep = null;
        java.sql.ResultSet res = null;
        connection = open();
        java.util.List<ConfiguracionflujosformuladosDTO> list=new java.util.ArrayList<ConfiguracionflujosformuladosDTO>();

        try{
            String QUERY = "SELECT ID, IDMODELO, PRODUCTO, FORMULA_CAPITAL, FORMULA_INTERES_FIJO, FORMULA_INTERES_VARIABLE, FORMULA_FECHA, ACTIVO FROM CONFIGURACIONFLUJOSFORMULADOS WHERE FORMULA_FECHA = ?";
            prep = connection.prepareStatement( QUERY );
            prep.setString (1,formula_fecha);
            res = prep.executeQuery();
            while (res.next()) {
                list.add(new ConfiguracionflujosformuladosDTO(  (Integer)res.getObject ( 1 ), (Integer)res.getObject ( 2 ), (String)res.getObject ( 3 ), (String)res.getObject ( 4 ), (String)res.getObject ( 5 ), (String)res.getObject ( 6 ), (String)res.getObject ( 7 ), (Integer)res.getObject ( 8 )));
            }
            return list;
        } catch (SQLException ex) {
            throw ex;
        } catch (Exception ex) {
            throw ex;
        } finally {
            close(prep,connection);
        }
    }

    /**
     * The List By activo
     * @param activo
     * @return list of ConfiguracionflujosformuladosDTO
     */
    public java.util.List<ConfiguracionflujosformuladosDTO> findByActivo (Integer  activo) throws SQLException, Exception, Throwable {

        java.sql.PreparedStatement prep = null;
        java.sql.ResultSet res = null;
        connection = open();
        java.util.List<ConfiguracionflujosformuladosDTO> list=new java.util.ArrayList<ConfiguracionflujosformuladosDTO>();

        try{
            String QUERY = "SELECT ID, IDMODELO, PRODUCTO, FORMULA_CAPITAL, FORMULA_INTERES_FIJO, FORMULA_INTERES_VARIABLE, FORMULA_FECHA, ACTIVO FROM CONFIGURACIONFLUJOSFORMULADOS WHERE ACTIVO = ?";
            prep = connection.prepareStatement( QUERY );
            prep.setInt (1,activo);
            res = prep.executeQuery();
            while (res.next()) {
                list.add(new ConfiguracionflujosformuladosDTO(  (Integer)res.getObject ( 1 ), (Integer)res.getObject ( 2 ), (String)res.getObject ( 3 ), (String)res.getObject ( 4 ), (String)res.getObject ( 5 ), (String)res.getObject ( 6 ), (String)res.getObject ( 7 ), (Integer)res.getObject ( 8 )));
            }
            return list;
        } catch (SQLException ex) {
            throw ex;
        } catch (Exception ex) {
            throw ex;
        } finally {
            close(prep,connection);
        }
    }





    /**
     * Open the connection
     * @return
     * @throws java.lang.ClassNotFoundException
     * @throws java.sql.SQLException
     */
    private java.sql.Connection open() throws ClassNotFoundException, java.sql.SQLException,Exception {
    	return getConnection("METRICADW");
    }



}
